package com.roc.web.action.merinfo;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.constant.Role;

public class FormalMerinfoSearchCondtion extends BaseSearchCondition{

	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String orgCode;//收单机构号
	private String loginId; //登录人id
	private String roleId; //角色 
	private long agentId;//所属代理商
	private String merchantNo;//商户号
	private String merchantName;//商户简称
	private String  merBrName; //注册名称
	private String status;//状态
	private int  merinfoFlag;//商户状态
	private String obj;//所有下级代理
	private String opId;//操作员id
	private String  certifName;//法人
	private String  cardNo; //法人身份证
	private String  contactName;//业务联系人
	private String  phone;//联系电话 
	private String areaCode;//地区
	private String  mccName;//mcc
	private String  rateType;//扣率类型
	private String  minDate;//入网时间
	private String  maxDate;//入网时间
	private String  midType;//商户类型
	private String  parentCode;//父类
	private String  sonCode;//子类
	private String  accountNo;//账号 
	private String  accountName;//账号 
	
	private String  cycle;//结算周期
	private String  addr;//商户地址
	private String realName;//所属代理商中文
	
	private String clearCardId; //结算人身份证
	private String directlyAgent; //直属代理商
	private String association;//关联关系
	private String busiLiceNo; //营业执照号码
	private String superRole; //上级角色
	private String oneOrgCode; //机构号
	private String groupCd; //受理市场标识
	@Override
	protected String getOrderPartOfHQL() {
		return " ORDER BY A.REC_CRT_TS DESC  ";
	}

	@Override
	protected String getRowCountHQL() {
		StringBuffer  sb= new StringBuffer();
		
		sb.append(" SELECT count(1)                                       \n ");
		sb.append("   FROM TBL_MCHT_INF A,                                               \n ");
		sb.append("        TBL_MCHT_BANKACCOUNT D,                                       \n ");
		sb.append("        (SELECT T1.INS_ID_CD, T1.INS_NAME FROM TBL_INS_INF T1) TI,    \n ");
		sb.append("        (SELECT T.ID,                                                 \n ");
		sb.append("                T.REAL_NAME,                                          \n ");
		sb.append("                (SELECT ac.name                                  \n ");
		sb.append("                   FROM tbl_area ac                                \n ");
		sb.append("                  WHERE ac.id=t.companyid) COMPANYNAME,  \n ");
		sb.append("                (SELECT TT.REAL_NAME                                  \n ");
		sb.append("                   FROM T_OPERATORS TT                                \n ");
		sb.append("                  WHERE TT.ID = TO_NUMBER(T.ONEAGENTID)) ONEAGENGNAME \n ");
		sb.append("           FROM T_OPERATORS T) OP                                     \n ");
		sb.append("  WHERE (A.GROUP_CD IS NULL OR A.GROUP_CD <> '3') AND A.MCHT_CD = D.OWNER_CD                                        \n ");
		sb.append("    AND  OP.ID = TO_NUMBER(A.EXT1)                                     \n ");
		sb.append("    AND A.BANK_BELONG_CD = TI.INS_ID_CD                     \n ");
        if(groupCd.equals("2"))
        {
        	sb.append("    AND A.group_cd='2'                    \n ");
        }
		//登录人角色销售人员                   
		if(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode())){
			sb.append(" and exists (select 1 from t_oper_role ar  where ar.agentid = a.uc_bc_cd_area   and ar.operid =").append(loginId).append(" )");        
		}		
		//角色为代理商或商服宝会员或父级id为代理商            
		else if(roleId.equals(Role.AGENT.getCode())||roleId.equals(Role.SFB_AGENT.getCode())){
            sb.append("  and exists (select 1   from TBL_MER_AGENT_RELATION tmar where TMAR.MERNO = A.MCHT_CD   and tmar.agentid =").append(loginId).append(" )");
  		}
		return sb.toString();
	}

	 
	@Override
	protected String getSearchHQL() {
		StringBuffer  sb= new StringBuffer();
	
		sb.append(" SELECT A.MCHT_CD,                                           \n ");
		sb.append("        A.NAME_BUSI,                                                  \n ");
		sb.append("        A.NAME MERNAME,                                               \n ");
		sb.append("        TI.INS_NAME,                                                  \n ");
		sb.append("        A.CONTACT_NAME,                                               \n ");
		sb.append("        A.CONTACT_PHONENO,                                            \n ");
		sb.append("        A.CERTIF,                                                     \n ");
		sb.append("        A.CERTIF_NO,                                                  \n ");
		sb.append("        A.BUSI_LICE_NO,                                               \n ");
		sb.append("        A.REG_ADDR,                                                   \n ");
		sb.append("        A.STATUS,                                                     \n ");
		sb.append("        (SELECT TP.REAL_NAME                                          \n ");
		sb.append("           FROM T_OPERATORS TP                                        \n ");
		sb.append("          WHERE TP.ID= to_number(A.ORI_CHNL_DESC)) RKNAME,           \n ");
		sb.append("        OP.COMPANYNAME,                                               \n ");
		sb.append("        OP.ONEAGENGNAME,                                              \n ");
		sb.append("        OP.REAL_NAME,                                                 \n ");
		sb.append("        D.NAME ACCOUNTNAME,                                           \n ");
		sb.append("        D.ACCOUNT,                                                    \n ");
		sb.append("        D.BANK_NAME,                                                  \n ");
		sb.append("        D.BANK_CODE,                                                  \n ");
		sb.append("        NVL2((SELECT DISTINCT PROD_CD                                 \n ");
		sb.append("               FROM TBL_MCHT_BIZ_DEAL t                               \n ");
		sb.append("              WHERE t.MCHT_CD = A.MCHT_CD                             \n ");
		sb.append("                AND t.PROD_CD = '1000'),                              \n ");
		sb.append("             'T1',                                                    \n ");
		sb.append("             ''),                                                     \n ");
		sb.append("        NVL2((SELECT DISTINCT PROD_CD                                 \n ");
		sb.append("               FROM TBL_MCHT_BIZ_DEAL t                               \n ");
		sb.append("              WHERE t.MCHT_CD = A.MCHT_CD                             \n ");
		sb.append("                AND t.PROD_CD = '1171'),                              \n ");
		sb.append("             'T0',                                                    \n ");
		sb.append("             ''),                                                     \n ");
		sb.append("        A.GROUP_CD,                                                   \n ");
		sb.append("        A.NATION_CD,                                                  \n ");
		sb.append("        A.ISGROUP,                                                    \n ");
		sb.append("        A.APPL_DATE,                                                   \n ");
		sb.append("        NVL2((SELECT DISTINCT PROD_CD                                 \n ");
		sb.append("               FROM TBL_MCHT_BIZ_DEAL t                               \n ");
		sb.append("              WHERE t.MCHT_CD = A.MCHT_CD                             \n ");
		sb.append("                AND t.PROD_CD = '3000'),                              \n ");
		sb.append("             '直连T1',                                                    \n ");
		sb.append("             ''),                                                     \n ");
		sb.append("        NVL2((SELECT DISTINCT PROD_CD                                 \n ");
		sb.append("               FROM TBL_MCHT_BIZ_DEAL t                               \n ");
		sb.append("              WHERE t.MCHT_CD = A.MCHT_CD                             \n ");
		sb.append("                AND t.PROD_CD = '1271'),                              \n ");
		sb.append("             '直连T0',                                                    \n ");
		sb.append("             '')                                                     \n ");
		if(roleId.equals(Role.SYSTEM.getCode())||roleId.equals(Role.OPERATORS_LEAD.getCode())
				||roleId.equals(Role.RISK_CONTROL_OPERATORS.getCode())||roleId.equals(Role.RISK_CONTROL_LEAD.getCode())||groupCd.equals("2")
			){
			sb.append("     ,nvl((select decode(ei.term_property,'0','免投','1','押金','2','销售') from tbl_mcht_extend_info ei where A.MCHT_CD=ei.mcth_cd ),'空') term_property, \n ");
			sb.append("     nvl((select ei.certif_start_date||'---'||ei.certif_end_date from tbl_mcht_extend_info ei where A.MCHT_CD=ei.mcth_cd ) ,'空') certif_date, \n ");
			sb.append("     nvl((select ei.busi_lice_start_date||'---'||ei.busi_lice_end_date from tbl_mcht_extend_info ei where A.MCHT_CD=ei.mcth_cd ),'空') busi_lice_date \n ");
			sb.append("   ,nvl((select os.real_name from t_operators os where to_char(os.id)= (select ei.develop_person from tbl_mcht_extend_info ei where A.MCHT_CD=ei.mcth_cd )),'空') develop_person  \n ");
		}
		sb.append("   FROM TBL_MCHT_INF A,                                               \n ");
		sb.append("        TBL_MCHT_BANKACCOUNT D,                                       \n ");
		sb.append("        (SELECT T1.INS_ID_CD, T1.INS_NAME FROM TBL_INS_INF T1) TI,    \n ");
		sb.append("        (SELECT T.ID,                                                 \n ");
		sb.append("                T.REAL_NAME,                                          \n ");
		sb.append("                (SELECT ac.name                                  \n ");
		sb.append("                   FROM tbl_area ac                                \n ");
		sb.append("                  WHERE ac.id=t.companyid) COMPANYNAME,  \n ");
		sb.append("                (SELECT TT.REAL_NAME                                  \n ");
		sb.append("                   FROM T_OPERATORS TT                                \n ");
		sb.append("                  WHERE TT.ID = TO_NUMBER(T.ONEAGENTID)) ONEAGENGNAME \n ");
		sb.append("           FROM T_OPERATORS T) OP                                     \n ");
		sb.append("  WHERE (A.GROUP_CD IS NULL OR A.GROUP_CD <> '3') AND A.MCHT_CD = D.OWNER_CD                                        \n ");
		sb.append("    AND OP.ID = TO_NUMBER(A.EXT1)                                     \n ");
	    sb.append("    AND A.BANK_BELONG_CD = TI.INS_ID_CD                     \n ");
	    if(groupCd.equals("2"))
        {
        	sb.append("    AND A.group_cd='2'                    \n ");
        }
	    //登录人角色销售人员                   
		if(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode())){
			sb.append(" and exists (select 1 from t_oper_role ar  where ar.agentid = a.uc_bc_cd_area   and ar.operid =").append(loginId).append(" )");        
		}		
		//角色为代理商或商服宝会员或父级id为代理商            
		else if(roleId.equals(Role.AGENT.getCode())||roleId.equals(Role.SFB_AGENT.getCode())){
            sb.append("  and exists (select 1   from TBL_MER_AGENT_RELATION tmar where TMAR.MERNO = A.MCHT_CD   and tmar.agentid =").append(loginId).append(" )");
  		}
		return sb.toString();
	}

	 
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
		
		//商户非法人身份证
		if(Constance.isNotEmpty(clearCardId)){
			filedBeanList.add(new WhereHQLFieldBean("A.STLM_WAY_DESC", clearCardId.trim(), "=", false));
		}
		//机构
		if(Constance.isNotEmpty(oneOrgCode)&&!roleId.equals(Role.SYSTEM.getCode())&&!roleId.equals(Role.AGENT.getCode())&&!(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode()))){//机构
			filedBeanList.add(new WhereHQLFieldBean("A.uc_bc_cd_area", oneOrgCode.trim(), "=", false));
		}
		//直属代理商
		if(Constance.isNotEmpty(directlyAgent)){
			filedBeanList.add(new WhereHQLFieldBean("OP.real_name", directlyAgent.trim(), "=", false));
		}
		//商户关联关系
		if(Constance.isNotEmpty(association)&&!"99".equals(association)){ 
			filedBeanList.add(new WhereHQLFieldBean("A.group_cd", association.trim(), "=", false));
		}
		
		//所属机构
		if(Constance.isNotEmpty(orgCode)){
			filedBeanList.add(new WhereHQLFieldBean("A.BANK_BELONG_CD", orgCode.trim(), "=", false));
		}
		
		if(Constance.isNotEmpty(merchantNo)){ //商户号
			filedBeanList.add(new WhereHQLFieldBean("A.MCHT_CD", merchantNo.trim(), "=", false));
		}
		 
		if(Constance.isNotEmpty(merBrName)){ //商户名称 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("A.NAME", merBrName.trim(), "like", false,"ALL"));
		}
		
		if(Constance.isNotEmpty(merchantName)){ //商户名称 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("A.NAME_BUSI", merchantName.trim(), "like", false,"ALL"));
		}
		 
		if(Constance.isNotEmpty(minDate)&&Constance.isNotEmpty(maxDate)){ //入网时间
			filedBeanList.add(new WhereHQLFieldBean("A.APPL_DATE",minDate.trim(),">=", false));
       	    filedBeanList.add(new WhereHQLFieldBean("A.APPL_DATE",maxDate.trim(),"<=", false));
		}
		if(Constance.isNotEmpty(opId) 
				&& Constance.isNotEmpty(roleId)  
				&& roleId.equals(Role.AGENT_BUSS.getCode())){//操作员 
	    	filedBeanList.add(new WhereHQLFieldBean("A.REC_UPD_OPR", opId.trim(), "=", false));
	    }
		
		if(Constance.isNotEmpty(certifName)){ //法人
			filedBeanList.add(new WhereHQLFieldBean("A.CERTIF", certifName.trim(), "like", false,"ALL"));
		}
		if(Constance.isNotEmpty(cardNo)){ //法人身份证号
			filedBeanList.add(new WhereHQLFieldBean("A.CERTIF_NO", cardNo.trim(), "=", false));
		}
		if(Constance.isNotEmpty(contactName)){ //业务联系人
			filedBeanList.add(new WhereHQLFieldBean("A.CONTACT_NAME", contactName.trim() ,"like", false,"ALL"));
		} 
		if(Constance.isNotEmpty(phone)){ //业务联系电话
			filedBeanList.add(new WhereHQLFieldBean("A.CONTACT_PHONENO", phone.trim(), "=", false));
		} 
		if(Constance.isNotEmpty(status) && !status.trim().equals("99")){ //状态
			filedBeanList.add(new WhereHQLFieldBean("A.STATUS", status.trim(), "=", false));
		}
		if(Constance.isNotEmpty(accountNo)){ //结算账号
			filedBeanList.add(new WhereHQLFieldBean("D.ACCOUNT", accountNo.trim(), "=", false));
		} 
		if(Constance.isNotEmpty(accountName)){ //结算账户名称
			filedBeanList.add(new WhereHQLFieldBean("D.NAME", accountName.trim() ,"like", false,"ALL"));
		}
		if(Constance.isNotEmpty(areaCode)){ //地区
			if(!areaCode.equals("99")){
				filedBeanList.add(new WhereHQLFieldBean("em.area_code", areaCode.trim(), "=", false));
			}
			
		}
		 
		/*//所属下级
		if(Constance.isNotEmpty(obj)){
			String allOp="(";
			String[] Strs=obj.split(",");
			int objLength=(Strs.length)/100;//整数
			int remainder=(Strs.length)%100;//余数
		    if(remainder>0 ){
				String str="";
				for (int i = 0; i <remainder; i++) {
					str=str+Strs[100*objLength+i];
					if(i<remainder-1){
						str=str+",";
					}
				}
				allOp=allOp+"A.EXT1 IN ("+str+")";
			}else if(remainder==0 && objLength>0){//整除
				String str="";
				for (int i = 0; i <objLength; i++) {
					for (int j = 0; j <100; j++) {
						str=str+Strs[i*100+j];
						if(j<99){
							str=str+"',";
						}
					}
					String or="";
					if(remainder==0 && objLength>1){or="OR";}
					allOp=allOp+or+" A.EXT1 IN ("+str+")";
				}
			}
		    allOp=allOp+")";
		    filedBeanList.add(new WhereHQLFieldBean(allOp));
			 
		} */
		 
		if(Constance.isNotEmpty(mccName)){//mcc
			filedBeanList.add(new WhereHQLFieldBean("e.mcc", mccName.trim(), "=", false));
		}
		if(Constance.isNotEmpty(rateType) && !rateType.equals("99")){ //扣率类型
			filedBeanList.add(new WhereHQLFieldBean("e.COMM_MODE", rateType.trim(), "=", false));
		}
		
		if(Constance.isNotEmpty(midType)&&!"99".equals(midType)){ //商户类型
			filedBeanList.add(new WhereHQLFieldBean("e.MER_KIND", midType.trim(), "=", false));
		}
		
		if(Constance.isNotEmpty(busiLiceNo)){	//营业执照号
			filedBeanList.add(new WhereHQLFieldBean("A.BUSI_LICE_NO", busiLiceNo.trim(), "=", false));
		}
		
		/*if(merinfoFlag==0 || merinfoFlag==2){ //商户状态
			String [] flag = {"0","2"};
	        filedBeanList.add(new WhereHQLFieldBean("e.flag IN("+Constance.getArrayContent(flag)+")"));
		}else if(merinfoFlag==3){
			filedBeanList.add(new WhereHQLFieldBean("e.flag", merinfoFlag, "=", false));
		}*/
		/*if(Constance.isNotEmpty(cycle)&&!"99".equals(cycle)){ //商户结算周期
			filedBeanList.add(new WhereHQLFieldBean("em.CYCLEID", cycle.trim(), "=", false));
		}
		if(Constance.isNotEmpty(addr)){ //商户地址 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("e.addr", addr.trim(), "like", false,"ALL"));
		}
		if(Constance.isNotEmpty(realName)){//代理商中文(模糊)
			filedBeanList.add(new WhereHQLFieldBean("op.REAL_NAME", realName.trim(), "like", false,"ALL"));
		}*/
        /*if(agentId>0){//代理商
        	filedBeanList.add(new WhereHQLFieldBean("em.AGENT_ID", agentId, "=", false));
        }*/
		return filedBeanList;
	}


	public String getMerchantNo() {
		return merchantNo;
	}

	public void setMerchantNo(String merchantNo) {
		this.merchantNo = merchantNo;
	}

	public String getMerchantName() {
		return merchantName;
	}

	public void setMerchantName(String merchantName) {
		this.merchantName = merchantName;
	}

	public String getAreaCode() {
		return areaCode;
	}

	public void setAreaCode(String areaCode) {
		this.areaCode = areaCode;
	}


	public String getMerBrName() {
		return merBrName;
	}

	public void setMerBrName(String merBrName) {
		this.merBrName = merBrName;
	}

	public String getCardNo() {
		return cardNo;
	}

	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	

	public String getMccName() {
		return mccName;
	}

	public void setMccName(String mccName) {
		this.mccName = mccName;
	}

	public String getRateType() {
		return rateType;
	}

	public void setRateType(String rateType) {
		this.rateType = rateType;
	}

	

	public String getMidType() {
		return midType;
	}

	public void setMidType(String midType) {
		this.midType = midType;
	}

	
	public String getCycle() {
		return cycle;
	}

	public void setCycle(String cycle) {
		this.cycle = cycle;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public long getAgentId() {
		return agentId;
	}

	public void setAgentId(long agentId) {
		this.agentId = agentId;
	}


	public void setMerinfoFlag(int merinfoFlag) {
		this.merinfoFlag = merinfoFlag;
	}

	public int getMerinfoFlag() {
		return merinfoFlag;
	}

	public String getRealName() {
		return realName;
	}

	public void setRealName(String realName) {
		this.realName = realName;
	}

	public String getRoleId() {
		return roleId;
	}

	public void setRoleId(String roleId) {
		this.roleId = roleId;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getObj() {
		return obj;
	}

	public void setObj(String obj) {
		this.obj = obj;
	}

	public String getOpId() {
		return opId;
	}

	public void setOpId(String opId) {
		this.opId = opId;
	}

	public String getCertifName() {
		return certifName;
	}

	public void setCertifName(String certifName) {
		this.certifName = certifName;
	}

	public String getContactName() {
		return contactName;
	}

	public void setContactName(String contactName) {
		this.contactName = contactName;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getParentCode() {
		return parentCode;
	}

	public void setParentCode(String parentCode) {
		this.parentCode = parentCode;
	}

	public String getSonCode() {
		return sonCode;
	}

	public void setSonCode(String sonCode) {
		this.sonCode = sonCode;
	}

	public String getAccountNo() {
		return accountNo;
	}

	public void setAccountNo(String accountNo) {
		this.accountNo = accountNo;
	}

	public String getAccountName() {
		return accountName;
	}

	public void setAccountName(String accountName) {
		this.accountName = accountName;
	}

	public String getClearCardId() {
		return clearCardId;
	}

	public void setClearCardId(String clearCardId) {
		this.clearCardId = clearCardId;
	}

	public String getDirectlyAgent() {
		return directlyAgent;
	}

	public void setDirectlyAgent(String directlyAgent) {
		this.directlyAgent = directlyAgent;
	}

	public String getAssociation() {
		return association;
	}

	public void setAssociation(String association) {
		this.association = association;
	}

	public String getBusiLiceNo() {
		return busiLiceNo;
	}

	public void setBusiLiceNo(String busiLiceNo) {
		this.busiLiceNo = busiLiceNo;
	}

	public String getSuperRole() {
		return superRole;
	}

	public void setSuperRole(String superRole) {
		this.superRole = superRole;
	}

	public String getLoginId() {
		return loginId;
	}

	public void setLoginId(String loginId) {
		this.loginId = loginId;
	}

	public String getOneOrgCode() {
		return oneOrgCode;
	}

	public void setOneOrgCode(String oneOrgCode) {
		this.oneOrgCode = oneOrgCode;
	}

	public String getGroupCd() {
		return groupCd;
	}

	public void setGroupCd(String groupCd) {
		this.groupCd = groupCd;
	}
	

}